package com.emar.adx.yiqifa.dao;

import com.emar.adx.yiqifa.util.FunctionUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Description:
 * @Author: xiaoliang
 * @CreateDate: 2016/3/23 14:23
 */
//@Repository
public class CityIpDao extends BaseSqlDAO{

    private static final Logger logger = LoggerFactory.getLogger(CityIpDao.class);

    public void insertCityCodes(){
        String sql = "select * from temp_cam ";

        List<Map<String,Object>> list = super.getJdbcTemplate().queryForList(sql);
        List<Map<String,Object>> finallist = new ArrayList<Map<String, Object>>();


        for(Map<String,Object> map : list){

            String city = (String) map.get("city_name");
            String[] strs = city.split("、");

            StringBuffer sb = new StringBuffer();
            for(String s : strs){
                String sql2 = "SELECT id FROM city c WHERE c.`CITY_NAME_CN` LIKE '"+s+"%' ";

                Map<String,Object> map2 = super.getJdbcTemplate().queryForMap(sql2);
                int cityId = Double.valueOf(map2.get("id").toString()).intValue();
                sb.append(",").append(cityId);
            }

            map.put("city_codes",sb.toString().replaceFirst(",",""));
            finallist.add(map);
        }


        for(Map<String,Object> map : finallist){
            String updateSql = "UPDATE temp_cam t SET t.`city_codes` = '"+map.get("city_codes")+"' WHERE t.`id` = "+ map.get("id");

            super.getJdbcTemplate().update(updateSql);

        }

    }

    public void insertFilter(){
        String sql = "SELECT campaign_id,city_codes FROM temp_cam";

        List<Map<String,Object>> list = super.getJdbcTemplate().queryForList(sql);

        String sql2 = "SELECT site_id FROM temp_site ";
        final List<Map<String,Object>> list2 = super.getJdbcTemplate().queryForList(sql2);

        final List<Map<String,Object>> finallist = new ArrayList<Map<String, Object>>();


        for(Map<String,Object> map : list){
            Map<String,Object> fmap = new HashMap<String, Object>();

            Object city = map.get("city_codes");
            Object cid = map.get("campaign_id");

            for(Map<String,Object> map2 : list2 ){
                Object sid = map2.get("site_id");

                fmap.put("city",city);
                fmap.put("cid",cid);
                fmap.put("sid",sid);
                finallist.add(fmap);
            }
        }

                StringBuffer sb = new StringBuffer("INSERT INTO filter_campaign_site_city ");
                sb.append("values(null,").append("?").append(",")
                        .append("?").append(",").append("0,")
                        .append("?").append(",")
                        .append("now(),now(),'valid'").append(")");

                super.getJdbcTemplate().batchUpdate(sb.toString(),new BatchPreparedStatementSetter(){

                    @Override
                    public void setValues(PreparedStatement ps, int i) throws SQLException {

                        Map<String,Object> map = finallist.get(i);

                        int cid = Integer.valueOf(map.get("cid").toString());
                        String city = map.get("city").toString();
                        int sid = Double.valueOf(map.get("sid").toString()).intValue();
                        ps.setInt(1, cid);
                        ps.setInt(2, sid);
                        ps.setString(3,city);
                    }

                    @Override
                    public int getBatchSize() {
                        return finallist.size();
                    }
                });
    }
}
